--    Author    : MAYANTING
--    Name      : ADM.ANA_SUST_DLMG_RISK_BAD_LOAN_MIG.HQL
--    Functions :
--    Purpose   : Daily saving the variation data from stg
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0       2018-05-28  MAYANTING          1.CREATE THE PROCEDURE
--
    
    INSERT OVERWRITE TABLE ADM.ANA_SUST_DLMG_RISK_BAD_LOAN_MIG PARTITION (DATA_DATE= '#V_DATA_DATE#')
    SELECT
           B.DATA_MONTH                                     AS DATA_MONTH
          ,A.DATA_MONTH                                     AS DATA_MONTH_Y
          ,AREA.AREA_CODE_1                                 AS AREA_CODE_1
          ,AREA.AREA_DSCR_1                                 AS AREA_DSCR_1
          ,AREA.AREA_CODE_3                                 AS AREA_CODE_2
          ,AREA.AREA_DSCR_3                                 AS AREA_DSCR_2
          ,AREA.AREA_CODE_4                                 AS AREA_CODE_3
          ,AREA.AREA_DSCR_4                                 AS AREA_DSCR_3
          ,B.FIVE_CLASS                                     AS LOAN_QUALITY
          ,A.FIVE_CLASS                                     AS LOAN_QUALITY_Y
          ,B.CCY                                            AS CCY
          ,SUM(B.LOAN_BAL)/100000000                        AS LOAN_BAL
    FROM
    (SELECT SUBSTR(DATA_DATE,1,6) AS DATA_MONTH,FIVE_CLASS,DUEBILL_NO FROM EDW.AG_LOAB_INFO WHERE DATA_DATE BETWEEN '#V_LAST_SEVENTEEN_MONTH_DATE#' AND '#V_PREV_MONTH_DATE#') A
    INNER JOIN
    (SELECT SUBSTR(DATA_DATE,1,6) AS DATA_MONTH,FIVE_CLASS,FIN_ORG_NO,DUEBILL_NO,CCY,COALESCE(ACTUAL_BAL,0) AS LOAN_BAL FROM EDW.AG_LOAB_INFO WHERE DATA_DATE='#V_DATA_DATE#') B
    ON A.DUEBILL_NO=B.DUEBILL_NO
    -- 与机构表最细级关联
    LEFT JOIN DIMENSION.T_ORG_BIZ_LVL ORG ON B.FIN_ORG_NO=ORG.ORG_CODE_4 AND ORG.IS_ACTIVE= '1' AND '#V_DATA_DATE#' BETWEEN ORG.START_DATE AND ORG.END_DATE
    -- 与地区表最细级关联
    LEFT JOIN (SELECT * FROM DIMENSION.T_ORG_AREA_RELATION WHERE AREA_LEVEL='3') REAL ON B.FIN_ORG_NO=REAL.ORG_ID AND '#V_DATA_DATE#' BETWEEN REAL.START_DATE AND REAL.END_DATE
    LEFT JOIN DIMENSION.T_AREA_CODE AREA ON REAL.AREA_CODE=AREA.AREA_CODE_4 AND '#V_DATA_DATE#' BETWEEN AREA.START_DATE AND AREA.END_DATE
    GROUP BY B.DATA_MONTH
            ,A.DATA_MONTH
            ,AREA.AREA_CODE_1
            ,AREA.AREA_DSCR_1
            ,AREA.AREA_CODE_3
            ,AREA.AREA_DSCR_3
            ,AREA.AREA_CODE_4
            ,AREA.AREA_DSCR_4
            ,B.FIVE_CLASS
            ,A.FIVE_CLASS
            ,B.CCY              ;